import pandas as pd
import sqlite3
import datetime
from Entity import Task, StartedTask
import time


class Tasklist:
    def __init__(self):
        self.tasks = []

    def get_list(self):
        return self.tasks

    def add_task(self, task):
        self.tasks.append(task)
        self.sorted_task()

    def delete_task(self, task_id):
        if not self.tasks:
            return None
        else:
            index = self.find_index(task_id)
            self.tasks.remove(index)
            self.sorted_task()

    def find_index(self, task_id):
        for task in self.tasks:
            if task.task_id == task_id:
                return self.tasks.index(task_id)
        return "error"

    def modify_task(self, task_id, attribute, information):
        index = self.find_index(task_id)
        task = self.tasks[index]
        if task:
            # 根据attribute修改任务属性的值
            if attribute == "description":
                task.description = information
            elif attribute == "category":
                task.category = information
            elif attribute == "priority":
                task.priority = information
                self.sorted_task()
        return task

    def sorted_task(self):
        self.tasks = sorted(self.tasks, key=lambda x: (x.get_priority()))
        return self.tasks

    def pop_task(self, task_id):
        index = self.find_index(task_id)
        started_task = self.tasks.pop(index)
        self.sorted_task()
        return started_task

    def find_task(self, task_id):
        index = self.find_index(task_id)
        return self.tasks[index]


class StartedTaskQueue:
    def __init__(self):
        self.started_tasks = []

    def append_task(self, started_task):
        self.started_tasks.append(started_task)

    def push_to_sql(self):
        dataframe = CalcServe.create_everyday_df("table_name")
        CalcServe.df_push_sql(dataframe, "db_file", "table_name")
        self.clear_queue()

    def clear_queue(self):
        self.started_tasks.clear()

    def __str__(self):
        return str(self.started_tasks)



started_queue = StartedTaskQueue()
tasklist = Tasklist()


class CalcServe:
    # 创建每日缓存表
    @staticmethod
    def create_everyday_df(table_name):
        # print("started_queue.started_tasks")
        # print(started_queue.started_tasks)
        data = {
            'user_id': [task.user_id for task in started_queue.started_tasks],
            'task_id': [task.task_id for task in started_queue.started_tasks],
            'description': [task.description for task in started_queue.started_tasks],
            'category': [task.category for task in started_queue.started_tasks],
            'priority': [task.priority for task in started_queue.started_tasks],
            'start': [task.start for task in started_queue.started_tasks],
            'during': [task.during for task in started_queue.started_tasks],
            'start_time_str': [task.start_time_str for task in started_queue.started_tasks],
            'end_time_str': [task.end_time_str for task in started_queue.started_tasks],
            'real_time': [task.real_time for task in started_queue.started_tasks],
            'finish': [task.finish for task in started_queue.started_tasks]
        }
        # print("data")
        # print(data)
        dataframe = pd.DataFrame(data)
        dataframe.name = table_name
        return dataframe

    # db_file 规定放入哪个文件夹，table_name 为上面创建的数据表起个名字
    @staticmethod
    def df_push_sql(dataframe, db_file_name, table_name):
        try:
            # 连接到数据库
            conn = sqlite3.connect(db_file_name)
            cursor = conn.cursor()
            # 查询数据库中已有的数据
            cursor.execute(f"SELECT * FROM {table_name}")
            existing_data = cursor.fetchall()
            # 转换数据库数据为数据帧
            # print("dataframe")
            # print(dataframe)
            existing_df = pd.DataFrame(existing_data, columns=dataframe.columns)
            # print("existing_df")
            # print(existing_df)
            # 将数据帧与数据库数据合并，并去除重复项
            merged_df = pd.concat([existing_df, dataframe])
            # print("merged_df")
            # print(merged_df)
            deduplicated_df = merged_df.drop_duplicates()
            # print("deduplicated_df")
            # print(deduplicated_df)
            # 清空数据库中的数据
            cursor.execute(f"DELETE FROM {table_name}")
            # 将去除重复项后的数据帧写入数据库
            deduplicated_df.to_sql(table_name, conn, if_exists='append', index=False)
            # 提交更改并关闭数据库连接
            conn.commit()
            conn.close()
            print(f"Data written to {table_name} in {db_file_name} successfully.")
        except Exception as e:
            print(f"Error writing data to database: {str(e)}")

    # 从数据库获取每日缓存表 返回dataFrame
    @staticmethod
    def get_df(data_time, freq, db_file_name, table_name):
        start_time_str, end_time_str = DateTools.cal_first_end_time(data_time, freq)
        if start_time_str is None or end_time_str is None:
            raise Exception("start_time_str or end_time_str = None")
        try:
            conn = sqlite3.connect(db_file_name)
            # 查询
            query = f"SELECT * FROM {table_name} WHERE start_time_str >= '{start_time_str}' " \
                    f"AND end_time_str <= '{end_time_str}' "
            # 读取数据
            dataframe = pd.read_sql_query(query, conn)
            conn.close()
            return dataframe
        except Exception as e:
            print(f"Error reading data from database: {str(e)}")
            return None

    @staticmethod
    def df_cal_focus_hour(date_time, freq, db_file, table_name):
        try:
            # 获取数据框架
            dataframe = CalcServe.get_df(date_time, freq, db_file, table_name)
            if dataframe is not None:
                real_time = dataframe['real_time']
                focus_hours = real_time.sum() / 60  # real_time单位：min 以小时为单位
                print(focus_hours)
                return focus_hours
            else:
                print(f"No data found for the specified {freq}.")
                return None
        except Exception as e:
            print(f"Error calculating focus hours: {str(e)}")
            return None

    # 根据缓存表计算完成的任务数量
    @staticmethod
    def df_cal_num_finished_task(date_time, freq, db_file, table_name):
        try:
            # 获取数据框架
            dataframe = CalcServe.get_df(date_time, freq, db_file, table_name)
            if dataframe is not None:
                # 计算专注时长
                num = dataframe['finish']
                # 专注时长求和
                num_task = num.sum()  # real_time单位：min 以小时为单位
                return num_task
            else:
                print(f"No data found for the specified {freq}.")
                return None
        except Exception as e:
            print(f"Error calculating number of finished tasks: {str(e)}")
            return None

    @staticmethod
    def df_cal_percentage(date_time, freq, db_file, table_name):
        try:
            # 获取数据框架
            dataframe = CalcServe.get_df(date_time, freq, db_file, table_name)
            # 确保数据框架不为空
            if dataframe is not None:
                # 计算总专注时长
                total_focus_minutes = dataframe['real_time'].sum()
                # 计算各任务的比例
                dataframe['percentage'] = (dataframe['real_time'] / total_focus_minutes) * 100.0
                # 返回任务比例数据框架
                return dataframe.set_index('category')['percentage'].to_dict()
            else:
                print("DataFrame is empty.")
                return None
        except Exception as e:
            print(f"Error calculating task percentages: {str(e)}")
            return None


# 处理时间的函数
class DateTools:
    @staticmethod
    def timestamp_to_datetime(date_time_str):
        try:
            if len(date_time_str) != 15:
                raise ValueError("输入字符串长度不正确")

            date_str = date_time_str[:8]
            time_str = date_time_str[8:].strip()

            if date_time_str[8] != ' ':
                raise ValueError("日期和时间之间需要有空格")

            parsed_date = datetime.datetime.strptime(date_str, '%Y%m%d')
            parsed_time = datetime.datetime.strptime(time_str, '%H%M%S')

            result_datetime = parsed_date.replace(hour=parsed_time.hour, minute=parsed_time.minute,
                                                  second=parsed_time.second)
            return result_datetime.strftime('%Y-%m-%d %H:%M:%S')
        except ValueError as e:
            return str(e)

    @staticmethod
    # 计算起始结束时间
    def cal_first_end_time(date_time_str, freq):
        start_time = None
        end_time = None
        date_time = datetime.datetime.strptime(date_time_str, "%Y-%m-%d %H:%M:%S")
        if freq == 'day':
            # 每日起始和结束时间
            start_time = datetime.datetime(date_time.year, date_time.month, date_time.day, 0, 0, 0)
            end_time = datetime.datetime(date_time.year, date_time.month, date_time.day, 23, 59, 59)
        elif freq == 'week':
            # 计算每周起始和结束时间
            today = date_time.date()
            start_time = today - datetime.timedelta(days=today.weekday())
            start_time = datetime.datetime(start_time.year, start_time.month, start_time.day,
                                           0, 0, 0)
            end_time = start_time + datetime.timedelta(days=6, hours=23, minutes=59, seconds=59)
        elif freq == 'month':
            # 计算每月起始和结束时间
            if date_time.month == 12:
                next_month = 1
                next_year = date_time.year + 1
            else:
                next_month = date_time.month + 1
                next_year = date_time.year
            start_time = datetime.datetime(date_time.year, date_time.month, 1, 0, 0, 0)
            end_time = datetime.datetime(next_year, next_month, 1, 0, 0, 0) - datetime.timedelta(seconds=1)
        else:
            print(f"{freq} is invalid, you should try day, week, month")
        start_time_str = start_time.strftime("%Y-%m-%d %H:%M:%S")
        end_time_str = end_time.strftime("%Y-%m-%d %H:%M:%S")
        return start_time_str, end_time_str

class AdminAndLogin:
    @staticmethod
    def create_user_table():
        conn = sqlite3.connect("user.db")
        cursor = conn.cursor()
        cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                        id INTEGER PRIMARY KEY,
                        username TEXT NOT NULL,
                        password TEXT NOT NULL)''')
        conn.commit()
        conn.close()
def test():
    db_file_name = 'db_file'
    table_name = 'table_name'
    conn = sqlite3.connect(db_file_name)
    cursor = conn.cursor()
    cursor.execute(f"DELETE FROM {table_name}")
    conn.commit()
    conn.close()

    input_str = "20200303 020212"
    input_time = DateTools.timestamp_to_datetime(input_str)
    print(input_time)
    start_value = 1
    # 创建任务列表和已完成任务列表

    # 添加任务到任务列表
    task1 = Task(user_id=1, task_id=1, description="Task 1", category="Work", priority=1, start=start_value, during=2)
    task2 = Task(user_id=1, task_id=2, description="Task 2", category="Personal", priority=1, start=start_value,
                 during=60)
    task3 = Task(user_id=1, task_id=3, description="Task 3", category="Dajiao", priority=1, start=start_value,
                 during=40)
    task4 = Task(user_id=1, task_id=4, description="Task 4", category="valorant", priority=1, start=start_value,
                 during=50)

    tasklist.add_task(task1)
    tasklist.add_task(task2)
    tasklist.add_task(task3)
    tasklist.add_task(task4)
    tasklist.sorted_task()

    # 保存开始后的任务
    start_time_str1 = "2023-05-01 09:06:00"
    end_time_str1 = "2023-05-01 09:08:00"
    end_time_str2 = "2023-06-02 09:08:00"
    end_time_str3 = "2023-05-01 15:08:00"
    end_time_str4 = "2023-05-01 18:08:00"
    finished_task1 = StartedTask(start_time_str1, end_time_str1,
                                 task1.user_id, task1.task_id, task1.description,
                                 task1.category, task1.priority, task1.during)
    finished_task2 = StartedTask(start_time_str1, end_time_str2,
                                 task2.user_id, task2.task_id, task2.description,
                                 task2.category, task2.priority, task2.during)
    finished_task3 = StartedTask(start_time_str1, end_time_str3,
                                 task3.user_id, task3.task_id, task3.description,
                                 task3.category, task3.priority, task3.during)
    finished_task4 = StartedTask(start_time_str1, end_time_str4,
                                 task4.user_id, task4.task_id, task4.description,
                                 task4.category, task4.priority, task4.during)
    started_queue.append_task(finished_task1)
    started_queue.push_to_sql()
    df = CalcServe.get_df("2023-05-01 09:06:00", "day", 'db_file', 'table_name')
    print("get_df")
    print(df)
    started_queue.append_task(finished_task2)
    started_queue.append_task(finished_task3)
    started_queue.append_task(finished_task4)
    started_queue.push_to_sql()
    df = CalcServe.get_df("2023-05-01 09:06:00", "day", 'db_file', 'table_name')
    print("get_df")
    print(df)
    # DB_PATH = 'dbfile'
    # conn = sqlite3.connect(DB_PATH)
    # cur = conn.execute('SELECT * FROM table_name')
    # values = cur.fetchall()
    # print(values)
    # cur.close()
    # conn.close()
    started_queue.append_task(finished_task2)
    started_queue.append_task(finished_task3)
    started_queue.append_task(finished_task4)
    started_queue.push_to_sql()
    df = CalcServe.get_df("2023-05-01 09:06:00", "day", 'db_file', 'table_name')
    print("get_df")
    print(df)
    # print((CalcServe.df_cal_percentage("2023-05-01 09:06:00", "month", 'db_file', 'table_name')))


if __name__ == "__main__":
    test()
